package com.itheima.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface ReportServiceMapper extends BaseMapper {
    @Select("select count(1) from member_t mt, rpt_date rd where mt.create_date=rd.fdate and mt.create_date=#{today} " +
            "union All " +
            "select count(1) from member_t mt, rpt_date rd where mt.create_date=rd.fdate and rd.fweek =#{weekNum} " +
            "union ALL " +
            "select count(1) from member_t mt, rpt_date rd where mt.create_date=rd.fdate and rd.fmonth=month(now()) ")
    List<Integer> findMemberCounts(@Param("today")String today,@Param("weekNum") Integer weekNum);

    @Select("select count(*) from order_t ot, rpt_date rd where ot.order_date=rd.fdate and ot.order_date=#{today} and ot.status = #{status}  " +
            "union All " +
            "select count(*) from order_t ot, rpt_date rd where ot.order_date=rd.fdate and rd.fweek= #{weekNum} and ot.status = #{status}  " +
            "union ALL " +
            "select count(*) from order_t ot, rpt_date rd where ot.order_date=rd.fdate and rd.fmonth=month(now()) and ot.status = #{status}  ")
    List<Integer> findOrderCounts(@Param("today")String today,@Param("weekNum") Integer weekNum,@Param("status") Integer status);


    @Select("select st.name,count(1) as setmeal_count,count(1)/(select sum(1) from order_t) as proportion from order_t ot, setmeal_t st where ot.setmeal_id = st.id group by st.name order by setmeal_count desc limit 0 , 3")
    List<Map> findHotSetmeal();


    /**
     *  根据开始结束日期（年月）查找每月注册会员数
     * @param startDate
     * @param endDate
     * @return
     */
    @Select("select count(mt.id) as totalCounts,concat(rd.fyear,\"-\",rd.fmonth) as yearAndMonth " +
            "from member_t mt " +
            "LEFT JOIN rpt_date rd on rd.fdate = mt.create_date " +
            "where DATE_FORMAT(mt.create_date,'%Y-%m')  BETWEEN #{startDate} and #{endDate} " +
            "group by rd.fmonth order by rd.id")
    List<Map> findMemberCountByDate(@Param("startDate") String startDate, @Param("endDate") String endDate);
}
